#!/bin/bash


mkdir -p /data/tools
cd /data/tools
echo 'downloading...'
wget -c  https://mirrors.huaweicloud.com/mysql/Downloads/MySQL-5.7/mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz || exit 1

tar zxvf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz

ln -s mysql-5.7.33-linux-glibc2.12-x86_64 mysql



# create work user
useradd -s /sbin/nologin -M mysql

# create datadir  data目录需要清空
rm -rf /data/data/mysql/
mkdir -p /data/data/mysql/
mkdir -p /data/logs/mysql/
mkdir -p /data/tools/mysql/tmp
mkdir -p /data/tools/mysql/status

# modify file and direcotry perm
chown mysql.mysql /data/tools/mysql -R
chown mysql.mysql /data/data/mysql -R
chown mysql.mysql /data/logs/mysql -R
chown mysql.mysql /data/tools/mysql/tmp -R
chown mysql.mysql /data/tools/mysql/status -R

chown mysql.mysql /data/tools/mysql-5.7.33-linux-glibc2.12-x86_64 -R

yum install -y  libaio-devel.x86_64

echo 'backup my.cnf'
mv /etc/my.cnf /etc/my.cnf_$(date +%s)

#cp mysql/support-files/my-default.cnf /etc/my.cnf
echo 'add new my.cnf'

cat >/etc/my.cnf <<EOF
[client]
port            = 3306

[mysql]
auto-rehash
prompt="\\u@\\h [\\d]>"
#pager="less -i -n -S"
#tee=/opt/mysql/query.log
socket                              =/tmp/mysql.sock                #	/tmp/mysql.sock

[mysqld]
####: for global
server_id                           =1                       	#	0
user                                =root                          #	mysql
basedir                             =/data/tools/mysql              #	/usr/local/mysql/
datadir                             =/data/data/mysql               #	/usr/local/mysql/data
socket                              =/data/tools/mysql/tmp/mysql.sock          #/tmp/mysql.sock
pid-file                            =/data/tools/mysql/status/mysql.pid
port                                =3306                           #	3306
bind-address                        = 0.0.0.0
character_set_server                =utf8mb4                           #	latin1
collation_server                    = utf8mb4_bin
init-connect                        = 'SET NAMES utf8mb4'

log_timestamps                      =system
default_time_zone		    ='+8:00'                   #	utc

read_only                           = 0                             #   off
super_read_only                     = 0
skip_name_resolve                   =off                             #   0
auto_increment_increment            =1                              #	1
auto_increment_offset               =1                              #	1
lower_case_table_names              =1                              #	0
secure_file_priv                    =/data/data/mysql/tmp/          #	null
open_files_limit                    =65536                          #   1024
max_connections                     =1000                           #   151
thread_cache_size                   =64                             #   9
table_open_cache                    =81920                          #   2000
table_definition_cache              =4096                           #   1400
table_open_cache_instances          =64                             #   16
max_prepared_stmt_count             =1048576                        #
explicit_defaults_for_timestamp=true
####: for binlog
binlog_format                       =row                          #	row
log_bin                             =/data/logs/mysql/mysql-bin                      #	off
binlog_rows_query_log_events        =on                             #	off
log_slave_updates                   =on                             #	off
expire_logs_days                    =7                              #	0
binlog_cache_size                   =65536                          #	65536(64k)
#binlog_checksum                     =none                           #	CRC32
sync_binlog                         =1                              #	1
slave-preserve-commit-order         =ON                             #

###： for sql_mode
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

####: for error-log
log_error                           =/data/logs/mysql/error.log      #	/usr/local/mysql/data/localhost.localdomain.err

general_log                         =off                            #   off
general_log_file                    =general.log                    #   hostname.log

####: for slow query log
slow_query_log                      =on                             #    off
slow_query_log_file                 =/data/logs/mysql/slow.log                       #    hostname.log
#log_queries_not_using_indexes       =on                             #    off
long_query_time                     =1.000000                       #    10.000000

####: for gtid
#gtid_executed_compression_period    =1000                          #	1000
gtid_mode                           =on                            #	off
enforce_gtid_consistency            =on                            #	off


####: for replication
skip_slave_start                     =1                              #
#master_info_repository              =table                         #	file
#relay_log_info_repository           =table                         #	file
slave_parallel_type                  =logical_clock                 #    database | LOGICAL_CLOCK
slave_parallel_workers               =4                             #    0
#rpl_semi_sync_master_enabled        =1                             #    0
#rpl_semi_sync_slave_enabled         =1                             #    0
#rpl_semi_sync_master_timeout        =1000                          #    1000(1 second)
#plugin_load_add                     =semisync_master.so            #
#plugin_load_add                     =semisync_slave.so             #
binlog_group_commit_sync_delay       =100                           #    500(0.05%秒)、默认值0
binlog_group_commit_sync_no_delay_count = 10                       #    0


####: for innodb
innodb_data_file_path                           =ibdata1:1024M:autoextend    #	ibdata1:12M:autoextend
innodb_temp_data_file_path                      =ibtmp1:12M:autoextend      #	ibtmp1:12M:autoextend
innodb_buffer_pool_filename                     =ib_buffer_pool             #	ib_buffer_pool
innodb_log_group_home_dir                       =./                         #	./
innodb_log_files_in_group                       =3                          #	2
innodb_log_file_size                            =100M                       #	50331648(48M)
innodb_file_per_table                           =on                         #	on
innodb_online_alter_log_max_size                =128M                       #   134217728(128M)
innodb_open_files                               =65535                      #   2000
innodb_page_size                                =16k                        #	16384(16k)
innodb_thread_concurrency                       =0                          #	0
innodb_read_io_threads                          =4                          #	4
innodb_write_io_threads                         =4                          #	4
innodb_purge_threads                            =4                          #	4(垃圾回收)
innodb_page_cleaners                            =4                          #   4(刷新lru脏页)
innodb_print_all_deadlocks                      =on                         #	off
innodb_deadlock_detect                          =on                         #	on
innodb_lock_wait_timeout                        =20                         #	50
innodb_spin_wait_delay                          =128                          #	6
innodb_autoinc_lock_mode                        =2                          #	1
innodb_io_capacity                              =200                        #   200
innodb_io_capacity_max                          =2000                       #   2000
#--------Persistent Optimizer Statistics
innodb_stats_auto_recalc                        =on                         #   on
innodb_stats_persistent                         =on                         #	on
innodb_stats_persistent_sample_pages            =20                         #	20

innodb_change_buffer_max_size                   =25                         #	25
innodb_flush_neighbors                          =1                          #	1
#innodb_flush_method                             =                           #
innodb_doublewrite                              =on                         #	on
innodb_log_buffer_size                          =128M                        #	16777216(16M)
innodb_flush_log_at_timeout                     =1                          #	1
innodb_flush_log_at_trx_commit                  =1                          #	1
innodb_buffer_pool_size                         =5000M                      #	134217728(128M)
innodb_buffer_pool_instances                    =4
#--------innodb scan resistant
innodb_old_blocks_pct                           =37                         #    37
innodb_old_blocks_time                          =1000                       #    1000
#--------innodb read ahead
innodb_read_ahead_threshold                     =56                         #    56 (0..64)
innodb_random_read_ahead                        =OFF                        #    OFF
#--------innodb buffer pool state
innodb_buffer_pool_dump_pct                     =25                         #    25
innodb_buffer_pool_dump_at_shutdown             =ON                         #    ON
innodb_buffer_pool_load_at_startup              =ON                         #    ON
innodb_flush_method				= O_DIRECT
EOF


## 替换basedir，同时也要在my.cnf中配置basedir，目录要一致
cd /data/tools/mysql
sed -i 's,/usr/local/mysql/data,/data/data/mysql,g' /data/tools/mysql/support-files/mysql.server
sed -i 's,/usr/local/mysql/bin,/data/tools/mysql/bin,g' /data/tools/mysql/support-files/mysql.server
# 这个替换最后执行
sed -i 's,/usr/local/mysql,/data/tools/mysql,g' /data/tools/mysql/support-files/mysql.server



# init mysql
echo "init mysql..."
bin/mysqld --initialize --basedir=/data/tools/mysql --datadir=/data/data/mysql --user=mysql
cp /data/tools/mysql/support-files/mysql.server /etc/init.d/mysql

echo "MySQL password is previous line !!!"

chmod +x /etc/init.d/mysql
echo "start mysql..."
/etc/init.d/mysql start
